﻿
--USE master
GO

--drop database Pompling
--CREATE DATABASE [Pompling]

GO

--USE [Pompling]

--drop table PomPomplingCustomers
CREATE TABLE PomPomplingCustomers
(
	[PomplingCustomerID] varchar(32),
	[UserName] VARCHAR(60),
	[Email] VARCHAR(100),
	[SecondaryEmail] VARCHAR(100),
	[PomplingPassword] VARCHAR(16),
	[IsCompany] BIT DEFAULT 0,
	[DateCustomerAdded] SMALLDATETIME DEFAULT GETDATE(),
	[IsSuspended] BIT DEFAULT 0,
	[IsSa] BIT DEFAULT 0,
	[AccountSuspendedDate] SMALLDATETIME NULL,
	CONSTRAINT [PK_PomplingCustomers_PomplingCustomerID ] PRIMARY KEY CLUSTERED (PomplingCustomerID ASC)
)
GO

CREATE TABLE PomPomplingCustomerLogins
(
	[PomPomplingCustomerLoginID] INT NOT NULL IDENTITY(1, 1),
	[PomplingCustomerID] varchar(32) NOT NULL CONSTRAINT [FK_PomPomplingCustomerLogins_PomplingCustomerID] FOREIGN KEY REFERENCES PomPomplingCustomers(PomplingCustomerID),
	[LoginDate] SMALLDATETIME DEFAULT GETDATE(),
	CONSTRAINT [PK_PomPomplingCustomerLogins_PomplingCustomerID ] PRIMARY KEY CLUSTERED (PomPomplingCustomerLoginID ASC)
)
GO

--alter table PomPomplingCustomers drop IX_PomPomplingCustomers_Email

CREATE NONCLUSTERED INDEX [IX_PomPomplingCustomers_Email] ON [PomPomplingCustomers]
(
	PomplingCustomerID ASC,
	Email ASC	 
)
CREATE NONCLUSTERED INDEX [IX_PomPomplingCustomers_Username] ON [PomPomplingCustomers]
(
	Username ASC	 
)

CREATE UNIQUE INDEX [UIX_PomPomplingCustomers_Username] ON [PomPomplingCustomers]
(
	Username ASC	 
)

--drop table PomPomplingAddresses
Create TABLE PomPomplingAddresses
(
	[PomplingAddressID] INT NOT NULL IDENTITY(1, 1),
	[PomplingCustomerID] varchar(32) NOT NULL CONSTRAINT [FK_PomPomplingAddresses_PomplingCustomerID] FOREIGN KEY REFERENCES PomPomplingCustomers(PomplingCustomerID),
	[AddressLine1] VARCHAR(60) NOT NULL,
	[AddressLine2] VARCHAR(60) NULL,
	[City] VARCHAR(60) NULL,
	[County] VARCHAR(60) NULL,
	[Postcode] VARCHAR(9) NULL,
	[AddressType] VARCHAR(10),	
	[DateAdded] Smalldatetime DEFAULT GETDATE(),
	Archived BIT DEFAULT 0,
	ArchivedDate SMALLDATETIME NULL,
	Visible BIT DEFAULT 1
	CONSTRAINT [PK_PomPomplingAddresses_AddressID] PRIMARY KEY CLUSTERED ([PomplingAddressID] ASC)
)

GO

--drop table [PomLookup]
Create TABLE PomLookup
(
	[PomLookupID] INT NOT NULL IDENTITY(1, 1),
	[LookupGroup] VARCHAR(60) NOT NULL,
	[LookupText] VARCHAR(100) NOT NULL,
	[DateAdded] Smalldatetime DEFAULT GETDATE(),
	 CONSTRAINT [PK_PomLookup_LookupID ] PRIMARY KEY CLUSTERED ([PomLookupID] ASC)
)


GO
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyType', 'Apartment');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyType', 'Semi Detached');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyType', 'Detached');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyType', 'Mid Terraced');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyType', 'End Terraced');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyType', 'Town House');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyType', 'Bungalow');

INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Entrance Hall');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Cloakroom');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Front Reception Room');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Rear Reception Room');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Through Lounge');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Kitchen');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Conservatory');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Open Plan Lounge/Kitchen');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Study');

INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Landing');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Bathroom');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Bedroom 1');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Bedroom 2');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Bedroom 3');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Bedroom 4');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Walk in Shower');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Shower Room');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'En Suite Bathroom');

INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Rear Garden');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Rear Yard');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Garage');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Driveway');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertyAttribute', 'Front Garden');

INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertySaleType', 'Let');
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertySaleType', 'For Sale'); 
INSERT INTO [PomLookup] ([LookupGroup], [LookupText]) VALUES ('PropertySaleType', 'Commercial'); 

GO

--drop table PomWebProperties
Create TABLE PomWebProperties
(
	[PomplingAddressID] INT NOT NULL CONSTRAINT [FK_PomWebProperties_Addresses_AddressID] FOREIGN KEY REFERENCES PomPomplingAddresses(PomplingAddressID),
	[PomplingCustomerID] varchar(32) NOT NULL CONSTRAINT [FK_PomWebProperties_PomplingCustomers_PomplingCustomerID] FOREIGN KEY REFERENCES PomPomplingCustomers([PomplingCustomerID]),
	[DateAdded] SMALLDATETIME DEFAULT GETDATE(),
	[NumberOfBedrooms] INT,
	[PropertyType] VARCHAR(40),
	[ShortDescription] VARCHAR(500),
	[LongDescription] VARCHAR(1500),
	 CONSTRAINT [PK_WebProperties_PomplingAddressID ] PRIMARY KEY CLUSTERED ([PomplingAddressID] ASC)
)

GO
--drop table PomRentalProperties
CREATE TABLE PomRentalProperties
(
	[PomplingAddressID] INT NOT NULL CONSTRAINT [FK_PomRentalProperties_WebProperties_PomplingAddressID] FOREIGN KEY REFERENCES PomWebProperties(PomplingAddressID),
	[PricePerCalendarMonth] DECIMAL(9,2) NULL,
	[Furnished] BIT DEFAULT 0,
	CONSTRAINT [PK_RentalProperties_PomplingAddressID ] PRIMARY KEY CLUSTERED (PomplingAddressID ASC)
)

GO
--drop table PomSaleProperties
CREATE TABLE PomSaleProperties
(
	[PomplingAddressID] INT NOT NULL CONSTRAINT [FK_PomSaleProperties_WebProperties_PomplingAddressID] FOREIGN KEY REFERENCES PomWebProperties(PomplingAddressID),
	[HousePrice] DECIMAL(9,2) NULL,
	CONSTRAINT [PK_SaleProperties_PomplingAddressID] PRIMARY KEY CLUSTERED (PomplingAddressID ASC)
)
GO

--drop table PomWebPropertiesAttributes
Create TABLE PomWebPropertiesAttributes
(
	[WebPropertiesAttributeID] INT NOT NULL IDENTITY(1, 1),
	[PomplingAddressID] INT NOT NULL CONSTRAINT [FK_PomWebProperties_WebPropertyID] FOREIGN KEY REFERENCES PomWebProperties(PomplingAddressID),
	[PropertyAttributeLookupID] INT NOT NULL CONSTRAINT [FK_PomLookup_PomLookupID]  FOREIGN KEY REFERENCES [PomLookup](PomLookupID),
	[LookupText] VARCHAR(100) NOT NULL,
	[AttributeDescription] VARCHAR(500),
	[DisplayPosition] SMALLINT DEFAULT 1,
	 CONSTRAINT [PK_WebPropertiesAttributes_WebPropertiesAttributeID ] PRIMARY KEY CLUSTERED ([WebPropertiesAttributeID] ASC)
)

GO

--drop table PomPropertyImages
Create TABLE PomPropertyImages
(
	[PomPropertyImagesID] INT NOT NULL IDENTITY(1, 1),
	[PomplingAddressID] INT NOT NULL CONSTRAINT [FK_PomPropertyImages_WebProperties_PomplingAddressID] FOREIGN KEY REFERENCES PomWebProperties(PomplingAddressID),
	[DisplayPicturePath] VARCHAR(100),
	[PictureTitle] VARCHAR(100), 
	[IsMainImage] BIT DEFAULT 0,
	[IsDeleted] BIT default 0,
	[DeletedDate] SMALLDATETIME,
	 CONSTRAINT [PK_PropertiesAdditionalImages_PropertiesAdditionalImageID] PRIMARY KEY CLUSTERED ([PomPropertyImagesID] ASC)
)

GO

